package server;
import gui.LibraryManager;

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;


import java.util.Calendar;
import java.util.Date;
import java.util.Properties;


import javax.mail.MessagingException;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;

import logic.BookItem;
import logic.BorrowHistory;
import logic.LateInReturnReport;
import logic.LendReport;
import logic.Librarian;
import logic.Message;
import logic.PdfFile;
import logic.PeriodicReport;
import logic.Reader;
import logic.Title;

/**class that responsible for comunicating with DATABASE*/
public class DBhandler { 
	/**connection to DB*/
	public Connection conn=null;
	public DBhandler() throws Exception {
	try 
	{
        Class.forName("com.mysql.jdbc.Driver").newInstance();
    } catch (Exception ex) {/* handle the error*/}
    
    try 
    {
    	
        conn = DriverManager.getConnection("jdbc:mysql://localhost/project_Lab","root","Braude");
        System.out.println("SQL connection succeed");
      

       BackroundPerforming bp=new BackroundPerforming(conn);
       bp.start();



 	} catch (Exception ex) 
 	    {/* handle any errors*/
        System.out.println("Exception: " + ex.getMessage());
        System.out.println("SQLState: " + ((SQLException) ex).getSQLState());
        System.out.println("VendorError: " + ((SQLException) ex).getErrorCode());
        
        }
	}
/**login to system
 * 
 * @param LoginDetails contains the login username and password
 * @return message class that contains the result of the login
 */
	public Message login( Reader LoginDetails) {
		Message m;
		Reader r;
		Statement stmt;
		PreparedStatement ps;
		String str,str2;
		str="SELECT ReaderID,UserName,Password,IsConnected,email,Cellphone,status FROM Reader Where UserName='"+ LoginDetails.getUsername()+"';";
		try {
			System.out.println("dbhandler");
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(str);
	 		
			if(!rs.next()){ //check if user exists
				
				rs.close();
				m=new Message ("user dosent exists",null);
				System.out.println("user dosent exists");
		 		return m; //user dosent exists
			}
			 r =new Reader(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(6),rs.getString(5),rs.getString(7));
	 			
			if(LoginDetails.getPassword().equals(rs.getString(3))){ //check if correct password
				if (rs.getString(7).equals("Locked"))
				{
					rs.close();
					m=new Message ("The user is Locked, cannot login.",null);
					return m; //password wrong
				}	
				else if(rs.getInt(4)==0) //check if user connected
				{
						ps = conn.prepareStatement("UPDATE Reader SET IsConnected=? WHERE ReaderID=?");	
							ps.setInt(1, 1);
							ps.setInt(2, rs.getInt(1));
							ps.executeUpdate();	
				}
				else{
						rs.close();
						 m=new Message ("user is already connected",null);
						return m; //user connected already	
				}
			}
			else{
					rs.close();
					m=new Message ("wrong password",null);
					return m; //password wrong
					
			}	

			
			str2="SELECT WorkerID,FirstName,LastName,WorkerType From Librarian Where ReaderID="+ r.getReaderId()+";";
			rs.close();
			ResultSet rs2 = stmt.executeQuery(str2);
			if(rs2.next())
			{
				if (rs2.getString(4).equals("Librarian"))
				{
					m=new Message("User Connected",new Librarian(rs2.getInt(1),rs2.getString(2),rs2.getString(3),r));
					return m; //Librarian permissions 
				}
				else if (rs2.getString(4).equals("Manager"))
				{
					m=new Message("User Connected",new LibraryManager(rs2.getInt(1),rs2.getString(2),rs2.getString(3),r));
					return m; //Library Manager permissions 
				}
				rs2.close();
			}
			else
			{
				rs2.close();
				m=new Message("User Connected",r);
				return m; //user connected
			}
			
		} 
		catch (Exception e) {
			e.printStackTrace();
			return new Message("error",null);
		}
		m=null;
		return m;
		
	}
/**adds a new reader to database
 * @param r contains the new reader details
 * @return acknowledgment that the reader was added
 */
	public int AddReader(Reader r) {
		System.out.println("reader");
		Statement stmt;
		
		
		String str;
       str="select * from Reader where UserName='"+r.getUsername()+"';";
       try 
		{
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(str);
	 		
			if(rs.next()){ //check if user exists
				
				rs.close();
				System.out.println("user exist");
		 		return 0; //user  exists
			}
		}
			catch (Exception e) {
				
				e.printStackTrace();
			}
		str="INSERT INTO  Reader (UserName, Password, Cellphone, email, IsConnected, status,Graduate) VALUES ('"+r.getUsername()+"','"+ r.getPassword()+"','"+ r.getCellphone()+"','"+r.getEmail()+"',0,'Activated','0');";
			
			try {
			
				stmt = conn.createStatement();
			

		 
			stmt.executeUpdate(str);
			return 1; //user added
		} catch (Exception e) {
			
			e.printStackTrace();
			
			return -1;
		}


	}
	/**log out of the server,connects to database and update isConnected field*/
	public Message logout(Reader r) {
		System.out.println("logout");
		PreparedStatement ps;
       try 
		{
	    	ps = conn.prepareStatement("UPDATE Reader SET IsConnected=? WHERE ReaderID=?");	
	    	ps.setInt(1, 0);
			ps.setInt(2, r.getReaderId());
			ps.executeUpdate();	
		 	
			
		}
		catch (Exception e) {
				
			e.printStackTrace();
			 return (new Message("Error",new Reader()));
		}
       return (new Message("logout done",null));
	}
	/**searches a book by name,subject,author
	 * @param m contains the search parameters
	 * @return an array of book objects
	 */
	public Message SearchBook(Message m) {
		
		Title t;
		 Date d;
		int i=0;
		String str = null;
		
		Statement stmt,stmt2;
		// allocates memory for 10 integers
		System.out.println("Search book function");
		
		if(m.getSearchType().equals("Author"))
        str="SELECT * FROM Book WHERE author like '%"+ m.getSearchContent()+"%';";
		
		if(m.getSearchType().equals("Book Name"))
		       str="SELECT * FROM Book WHERE bookName like '%"+ m.getSearchContent()+"%';";
		
		if(m.getSearchType().equals("Free"))
		       str="SELECT * FROM Book WHERE bookName like '%"+ m.getSearchContent()  +"%' or  author  like '%"+m.getSearchContent()+"%' or  description  like '%"+m.getSearchContent()+"%' or Subject like '%"+ m.getSearchContent()  +"%';";
		
		if(m.getSearchType().equals("Subject"))
		       str="SELECT * FROM Book WHERE Subject like '%"+ m.getSearchContent()  +"%';";
		
		
		System.out.println(str);
       try 
		{
    	   int numrows1;
    	   ResultSet ns ;
			stmt = conn.createStatement();
			stmt2=conn.createStatement();
			ResultSet rs = stmt.executeQuery(str);
		if(!rs.next()){ //check if book exists
				
				rs.close();
				m=new Message ("Book not found,try again",null);
				System.out.println("Book not found,try again");
		 		return m; //book dosen't exists
			}
			rs.last();
			numrows1=rs.getRow();
			rs.beforeFirst();
			System.out.println("n:"+numrows1);
			
			Object[][] obj=new Object[numrows1][2] ;
			
			
			  while(rs.next()){ //check if user exists
				  t= new Title();
				 t.setCatalogNumber(rs.getInt(1));
				 t.setName(rs.getString(2));
				 t.setAuthor(rs.getString(3));
				 t.setSubject(rs.getString(6));
				 t.setDescription(rs.getString(7));
				 t.setShelfLocation(rs.getString(10));
				 
				String fileName=rs.getString(11);
				
				int fileSize=rs.getInt(12);
				 t.setInStorage(rs.getInt(14));
				
			
				System.out.println("book name:"+t.getName());
				 if(fileName==null)
				 {
					 
					t.setTableOfContents(null);
				 }
				 else 
				 {
					PdfFile pf=new PdfFile(fileName,fileSize,this.getPDFData(t.getCatalogNumber()));
					 t.setTableOfContents(pf);
				 }
				 
				 
				 if(rs.getInt(14)==0) //if inStorage==0
				 {
					 
					 str="SELECT MIN(returnDate) FROM BorrowHistory WHERE CatalogNumber='" +t.getCatalogNumber()+"';"; 
					 ns = stmt2.executeQuery(str);
					
					 if(ns.next()){
					 d=ns.getDate(1);
					 obj[i][1]=d.toString();
					 ns.close();
					 }
				 }
				 else
				 {
					 obj[i][1]="0";
				 }
				 obj[i][0]=t;
				
				i++;
				 
			  }
				rs.close();
				
				m=new Message ("Book found",obj);
				
		 		return(m);
			
		  
		       }
			catch (Exception e) {
				
				e.printStackTrace();
				
			}
	     
       return new Message("error",null);

	}
	/** get book pdf and convert to byte array
	 * @param bookid
	 * @return  byte array pdf
	 */
public byte[] getPDFData(int bookid) {
	   byte [] fileData = null;
        byte[] fileBytes;
        String query;
        try {
            query = 
              "select TableOfContents from Book where CatalogNumber ='"+bookid+"'"; 
            Statement state = conn.createStatement();
            ResultSet rs = state.executeQuery(query);
            if (rs.next()) {
                fileBytes = rs.getBytes(1);
                String path="c://out//1.pdf";
                File f=new File(path);
                if (f.exists()) 
                	f.delete();
                OutputStream targetFile=  new FileOutputStream(path);
                targetFile.write(fileBytes);
                targetFile.close();
            }
            
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        File file=new File("c://out//1.pdf");
  	  FileInputStream d = null;
  	try {
  		d = new FileInputStream(file);
  	 fileData = new byte[(int)file.length()];
  	  DataInputStream dis = new DataInputStream (d);
  	  
  		dis.readFully(fileData);
  		dis.close();
  	} catch (IOException e2) {
  		
  		e2.printStackTrace();
  	}
        return fileData;
        
        
        
    }

/**edit a book 
 * @param title contains entire book with edited details
 * @return confirmation message
 */
	public Message EditBook(Title title) {
		InputStream fis = null;
		Message m = null;
		PreparedStatement ps;
		System.out.println( "the id is:" + title.getCatalogNumber());
		try {
			ps = conn.prepareStatement("UPDATE Book SET  bookName=?, author=?, editionNum=?, printDate=?, Subject=?, description=?,  PurchaseDate=?, ShelfLocation=?, filename=?,filesize=?,tableOfContents=?,Wanted=?,NumberOfCopies=? WHERE  CatalogNumber =?");
			ps.setString(1, title.getName());
			ps.setString(2, title.getAuthor());
			ps.setString(3, title.getEditionNum());
			Calendar cal = Calendar.getInstance();
		    cal.setTime(title.getPrintDate());
		    ps.setDate(4, new  java.sql.Date(cal.getTimeInMillis()));
			ps.setString(5, title.getSubject());
			ps.setString(6, title.getDescription());
			cal.setTime(title.getPurchaseDate());
			ps.setDate(7, new  java.sql.Date (cal.getTimeInMillis()));
			ps.setString(8, title.getShelfLocation());

			if(title.getTableOfContents() == null)
			{
			    ps.setString(9, null);
			    ps.setInt(10, -1);
				ps.setBinaryStream(11,null, 0); 
			 }
			else
			{
				
				 File file=new File("c://in//temp.pdf");
				  byte mybytearray[] = title.getTableOfContents().getFIS();
					
					// set output stream to write file in file system
				    BufferedOutputStream bos = null;
					try {
						bos = new BufferedOutputStream(new FileOutputStream(file));
					} catch (FileNotFoundException e1) {
						
						e1.printStackTrace();
					} 
				   
				    try {
						bos.write(mybytearray, 0 , mybytearray.length);
						 bos.flush(); //close buffer
						    bos.close();
						  
						
						    file=new File("c://in//temp.pdf");
							 fis = new FileInputStream(file);
					} catch (IOException e) {
						
						e.printStackTrace();
						return new Message("error",null);
					} //write out file
			
				    ps.setString(9, title.getTableOfContents().getFileName());
			    ps.setInt(10, title.getTableOfContents().getFileSize());
				ps.setBinaryStream(11,fis, title.getTableOfContents().getFileSize()); 
				
				
			}
			ps.setBoolean(12, title.getWanted());
			ps.setInt(13, title.getCopies());
			ps.setInt(14, title.getCatalogNumber());
			ps.executeUpdate();	
		} catch (Exception e1) {
			
			e1.printStackTrace();
			return new Message("error",null);
		}	

       m=new Message("Book Edited Succesefully",null);
		
		
		return m;
	}
/**
 * adds a new book to database and also state how much in storage
 * @param title contains the new book to be added
 * @return confirmation message
 * @throws IOException
 */
	public Message AddBook(Title title) throws IOException {
		int len ;
		InputStream fis;
		File file;
		Message m = null;
		String str="", filename;
		PreparedStatement ps;
		Statement stmt;
		ResultSet rs;
		
		str="SELECT * FROM Book WHERE bookName ='"+ title.getName()+"';";
		try {
			stmt=conn.createStatement();
			 rs = stmt.executeQuery(str);
			if(rs.next()){
				m=new Message("Book Name already exists",null);
				return m;
				}
			rs.close();
		} catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}

		
		if(title.getTableOfContents()==null )
			{
			
			fis=null;
		    len=-1;
		    filename=null;
			}
		else
			{
			  file=new File("c://in//1.pdf");
			  
			  
			  byte mybytearray[] = title.getTableOfContents().getFIS();
				
				// set output stream to write file in file system
			    BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(file)); 
			   
			    bos.write(mybytearray, 0 , mybytearray.length); //write out file
			    bos.flush(); //close buffer
			    bos.close();
			
			len =title.getTableOfContents().getFileSize();
			filename= title.getTableOfContents().getFileName();
			file=new File("c://in//1.pdf");
			fis = new FileInputStream(file);
			} 
		
			
			
			
			
		Calendar cal = Calendar.getInstance();
	    cal.setTime(title.getPrintDate());
	    
		try {
			ps = conn.prepareStatement("INSERT INTO Book (bookName, author, editionNum, printDate, Subject, description, NumberOfCopies, PurchaseDate, ShelfLocation,filename,filesize,tableOfContents,Wanted,inStorage)VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? ,?,?,?,?,?) ");
			ps.setString(1, title.getName());
			ps.setString(2, title.getAuthor());
			ps.setString(3, title.getEditionNum());
			ps.setDate(4,  new  java.sql.Date(cal.getTimeInMillis()));
			ps.setString(5, title.getSubject());
			ps.setString(6, title.getDescription());
			ps.setInt(7, title.getCopies());
			cal.setTime( title.getPurchaseDate());
			ps.setDate(8,  new  java.sql.Date(cal.getTimeInMillis()));
			ps.setString(9, title.getShelfLocation());
			ps.setString(10,filename);
	        ps.setInt(11, len);
            ps.setBinaryStream(12, fis, len); 
            ps.setBoolean(13,title.getWanted());
            ps.setInt(14,title.getCopies());
			ps.executeUpdate();	
	
			
		} catch (Exception e1) {
		e1.printStackTrace();
		return new Message("error",null);
		}	

       m=new Message("Book Added",null);
		
		
		return m;
	}
	
	
/**delete's book from database
 * @param bookItem the bookcatalog to be deleted
 * @return deleted confirmation
 */
	public Message DeleteBook(BookItem bookItem) {
		Message m = null;
		ResultSet rs = null;
		PreparedStatement ps;

		  String query;
	       
	            query =  "SELECT inStorage,NumberOfCopies from Book where CatalogNumber ='"+bookItem.getCatalogNumber()+"'"; 
	            Statement state;
				try {
					state = conn.createStatement();
					rs = state.executeQuery(query);
				} catch (Exception e) {
					
					e.printStackTrace();
					return new Message("error",null);
				}
				try {
					rs.next();
					if(rs.getInt(2)==rs.getInt(1)){
						ps = conn.prepareStatement("Delete from Book Where CatalogNumber=? ");
						ps.setInt(1, bookItem.getCatalogNumber());			
						ps.executeUpdate();	
						m=new Message("Book Deleted",null);
						return m;
					}
				} catch (Exception e) {
					
					e.printStackTrace();
					return new Message("error",null);
				}
				
		m=new Message("Book Not Deleted",null);
		
		return m;
	}
/**adds an order 
 * @param m is an object array, first array index is BookItem(contains the book catalog),
 * second array index is the reader which will be assosciated to this order
 * @return order confirmation
 */
	public Message orderBook(Object[] m) {
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");
		Message m1 = null;
		Reader r,r1;
	    BookItem b;
		Title t;
		r=(Reader)m[1];
		b=(BookItem)m[0];
		int countBooks = 0;
		Statement stmt;
		
		String str="";
		Date today = new Date();
		m1=(Message)GetBook(b);
		r1=(Reader)((Message)getReader(r.getReaderId())).getObjects();
		if(r1.getStatus().equals("Frozen")){
			return new Message("Cant Order Book,User is Frozen!",null);
		}
		t=(Title) m1.getObjects();
		str="Select count(OrderId) from Orders where CatalogNumber='"+t.getCatalogNumber() +"'";
		System.out.println(str);
		   
		try {
			   
			   stmt = conn.createStatement();
			   ResultSet rs1 = stmt.executeQuery(str);
			   rs1.next();
				countBooks=rs1.getInt(1);
			} catch (Exception e1) {
				
				e1.printStackTrace();
				return new Message("error",null);
			}
		   if(countBooks>=t.getCopies())
		   {
			   m1=new Message ("Cant Order book,Order List full",null);
		 		return m1; //No Space in queue
		   }
		
		
		str="INSERT INTO  Orders(OrderId ,ReaderID ,CatalogNumber,date,daytime,notified) VALUES (NULL,'"+r.getReaderId()+"','"+t.getCatalogNumber()+"','"+dateFormat.format(today)+"','"+ timeFormat.format(today) +"',0);";//'"+r.getReaderId()+"','"+ t.getCatalogNumber()+"'); ";
		System.out.println("r:"+r.getReaderId()+" b:"+ t.getCatalogNumber());
		
		
		try {
		
			stmt = conn.createStatement();
			 stmt.executeUpdate(str);
			
			
			
		} catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}
		m1=new Message("Order Added",null);
		return (m1);
  }
/**
 * gets entire book object(if exists)
 * @param bookItem contains the book catalog
 * @return a book object
 */
	public Message GetBook(BookItem bookItem) {
		Message m;
		Title t;
		String str = null;
		ResultSet rs = null;
		Statement stmt;
		int fileSize;
		str="SELECT * FROM Book WHERE CatalogNumber='" +bookItem.getCatalogNumber()+"';";
        try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(str);
		} catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}
		
	try {
		if(!rs.next()){ //check if user exists
				
				rs.close();
				m=new Message ("Book not found,try again",null);
				System.out.println("Book not found,try again");
		 		return m; //user dosent exists
			}
		else{
			
			t=new Title();
			t.setCatalogNumber(bookItem.getCatalogNumber());
			t.setName(rs.getString(2));
			t.setAuthor(rs.getString(3));
			t.setEditionNum(rs.getString(4));
			t.setPrintDate(rs.getDate(5));
			t.setSubject(rs.getString(6));
			t.setDescription(rs.getString(7));
			t.setCopies(Integer.parseInt(rs.getString(8)));
			t.setPurchaseDate(rs.getDate(9));
			t.setShelfLocation(rs.getString(10));
			t.setInStorage(rs.getInt(14));
			t.setWanted(rs.getBoolean(15));
			String fileName=rs.getString(11);
			fileSize=rs.getInt(12);
			
			 if(fileName!=null)
			 {
			 PdfFile pf=new PdfFile(fileName,fileSize,this.getPDFData(bookItem.getCatalogNumber()));
			 t.setTableOfContents(pf);
			 }
			
			m=new Message("Book found",t);
			return m;

		}
	} catch (Exception e) {
		
		e.printStackTrace();
		
	}
	return new Message("error",null);
	}
	@SuppressWarnings("deprecation")
	/**adds a borrow record to borrowhistory
	 * @param r contains reader that borrowes the book
	 * @param catalogNumber contains the book catalog to be lent
	 * @return book lented confirmation
	 */
	public Message Lend(Reader r,  BookItem catalogNumber) {
		Title t;
		Message m;
		String str="";
		PreparedStatement ps;
		java.util.Date today = new java.util.Date();
		java.util.Date returnDate= new java.util.Date();
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		new SimpleDateFormat("HH:mm:ss");
		int day;
		int countBooks=0;
		Statement stmt;
		ResultSet rs1 = null ;
		ResultSet rs2=null;
		int orderId=-1;

		m=getReader(r.getReaderId());
		if(m.getCommand().equals("The user dont exist"))
		{
			m=new Message ("The user dont exist",null);
	 		return m; //user dosent exists
		}
		r=(Reader)m.getObjects();
		System.out.println("readername:"+r.getUsername()+" "+r.getStatus());
		if(r.getStatus().equals("Activated"))
  		{
  			
		System.out.println("lend login Dbhandler "+r.getReaderId() + "  "+ catalogNumber.getCatalogNumber());
       m=GetBook(catalogNumber);
       
       System.out.println("masage:"+m.getCommand());
       if(m.getCommand().equals("Book found"))
       {
    	  t=(Title)m.getObjects();
    	   
       System.out.println("storage"+t.getInStorage());
    		   if(t.getInStorage()>0)
    		   {
    			   
    			     str="SELECT count(OrderId) From Orders Where CatalogNumber ='"+t.getCatalogNumber() +"'; ";
    		           System.out.println(str);
    		        try {
    		          stmt = conn.createStatement();
    		           rs1 = stmt.executeQuery(str);
    		           
    		           if (rs1.next())
    		           {
    		          
    		        	   countBooks=rs1.getInt(1);
    		        	   
    		        	  if(t.getInStorage()<=countBooks)
    		        	  {
    		        		  str="SELECT min(daytime),ReaderID,OrderId From Orders Where CatalogNumber ='"+t.getCatalogNumber() +"';";
    		        		  stmt = conn.createStatement();
    		        		  rs2 = stmt.executeQuery(str);
    		        		  
    		        		  if(rs2.next())
    		        		  {
    		        			  if(rs2.getInt(2)==r.getReaderId())
    		        			  {
    		        				  orderId=rs2.getInt(3);
    		        				   if(t.getWanted())
    		        	    		   {
    		        	    			   returnDate.setDate(today.getDate()+3);
    		        	    		   day=3;
    		        	    		   }
    		        	    		   else
    		        	    		   {
    		        	    			   returnDate.setDate(today.getDate()+14);  
    		        	    			   day=14;
    		        	    		   }
    		        	    		   
    		        	    		   
    		        	    		
    		        	    		
    		        	    		   str="INSERT INTO  BorrowHistory (Id_Borrow,CatalogNumber,ReaderID,borrowDate,returnDate,actualReturnDate,Comments) VALUES (NULL,'"+t.getCatalogNumber() +"','"+r.getReaderId() +"','"    +dateFormat.format(today)+"','"+dateFormat.format(returnDate)+"',NULL,NULL);";//'"+r.getReaderId()+"','"+ t.getCatalogNumber()+"'); ";
    		        	    	System.out.println(str);
    		        	    		   try {
    		        					stmt = conn.createStatement();
    		        					 stmt.executeUpdate(str);
    		        					
    		        				} catch (Exception e1) {
    		        					
    		        					e1.printStackTrace();
    		        					return new Message("error",null);
    		        				}
    		        					
    		        	    		   
    		        	    		   
    		        	    			try {		
    		        	    				
    		        						ps = conn.prepareStatement("UPDATE Book SET inStorage=? WHERE CatalogNumber=?");
    		        						ps.setInt(1, t.getInStorage()-1);
    		        		    			ps.setInt(2, t.getCatalogNumber());
    		        		    			ps.executeUpdate();	
    		        		    		ps.close();
    		        	    			} catch (Exception e) {
    		        						
    		        						e.printStackTrace();
    		        					}	
    		        	    			ps = conn.prepareStatement("Delete from Orders Where OrderId=? ");
    		    						ps.setInt(1, orderId);			
    		    						ps.executeUpdate();	
    		        	    			
    		        	    			m=new Message("The Book lend Successfully for"+String.format("%d", day)+"Day", null);
    		        	    		   return (m);
    		        			  }
    		        			  
    		        		  }
    		        		  
    		        		  
    		        	   m=new Message ("The book has waiting orders, cant lend book!",null);
    		             return m; //user dosent exists 
    		              }
    		           }
    		         } catch (Exception e1) {
    		          
    		          e1.printStackTrace();
    		         }
    		        
    		          
    		          
    		          
    		   if(t.getWanted())
    		   {
    			   returnDate.setDate(today.getDate()+3);
    		   day=3;
    		   }
    		   else
    		   {
    			   returnDate.setDate(today.getDate()+14);  
    			   day=14;
    		   }
    		   str="INSERT INTO  BorrowHistory (Id_Borrow,CatalogNumber,ReaderID,borrowDate,returnDate,actualReturnDate,Comments) VALUES (NULL,'"+t.getCatalogNumber() +"','"+r.getReaderId() +"','"    +dateFormat.format(today)+"','"+dateFormat.format(returnDate)+"',NULL,NULL);";//'"+r.getReaderId()+"','"+ t.getCatalogNumber()+"'); ";
    	System.out.println(str);
    		   try {
				stmt = conn.createStatement();
				 stmt.executeUpdate(str);
				
			} catch (Exception e1) {
				
				e1.printStackTrace();
				return new Message("error",null);
			}
				
    		   
    		   
    			try {		
    				
					ps = conn.prepareStatement("UPDATE Book SET inStorage=? WHERE CatalogNumber=?");
					ps.setInt(1, t.getInStorage()-1);
	    			ps.setInt(2, t.getCatalogNumber());
	    			ps.executeUpdate();	
	    		
    			} catch (Exception e) {
					
					e.printStackTrace();
					return new Message("error",null);
				}	
    	    	
    			
    			m=new Message("The Book lend Successfully for"+String.format("%d", day)+"Day", null);
    		   return (m);
    		}
    		   else //the book is not in stuck
    		   {
    			   m=new Message("The Book is not in stock", null);
    			   return m;
    		   }
    		   
    		   
    		   
     }
       else
       {
       m=new Message("The Catalog Number does not exist", null);
		return m;
	  }
    }
		m=new Message("The Reader Status is not Activated", null);
return m;
  }	
	/**change status of reader to Frozen
	 * @param reader contains the reader to be Frozen
	 * @return confirmation message
	 */
	public Message Freeze(Reader reader) {
		
	    Message m;
		PreparedStatement ps;
		System.out.println("readerId"+reader.getReaderId());
		m=getReader(reader.getReaderId());
		if(m.getCommand().equals("The user doesen't exist"))
		{
			return m;
		}
				
		
		try {		
			
			ps = conn.prepareStatement("UPDATE Reader SET status=? WHERE ReaderID=?");
			ps.setString(1,"Frozen");
			ps.setInt(2, reader.getReaderId());
			ps.executeUpdate();	
		
		} catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}	
		m=new Message ("The Reader is frozen Successfully",null);
		return m;
	}
	/**gets reader 
	 * @param readerId contains the reader id to get
	 * @return message contains reader object
	 */
	public Message getReader(int readerId)
	{   
		Message m;
	Reader r=new Reader();
	String str;
	ResultSet rs1 = null ;
	Statement stmt;
		str="SELECT * FROM Reader WHERE ReaderID='" +readerId+"';";
        try {
			stmt = conn.createStatement();
			rs1 = stmt.executeQuery(str);
		} catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}
		
	try {
		if(!rs1.next()){ //check if user exists
				
				rs1.close();
				m=new Message ("The user dont exist",null);
		 		return m; //user dosent exists
			}
		else
		{
		r.setReaderId(rs1.getInt(1));
		r.setUsername(rs1.getString(2));
		r.setPassword(rs1.getString(3));
		r.setCellphone(rs1.getString(4));
		r.setEmail(rs1.getString(5));
		r.setGraduate(rs1.getBoolean(6));
		r.setIsConnected(rs1.getInt(7));
		r.setStatus(rs1.getString(8));
		 }
		}
	 catch (Exception e) {
		
		e.printStackTrace();
		return new Message("error",null);
	}
	m=new Message ("The user exist",r);
	return(m);
		
	}
/**
 * returns book to storage and changes status as needed (Frozen\Locked\Activated)
 * @param r2 reader that returns the book
 * @param b contains the book catalog to return
 * @param disc contains the return description (if librarian specified)
 * @return book returned and status confirmation
 */
	public Message ReturnBook(Reader r2, BookItem b,String disc) {
		Reader r;
		Message m;
		String str;
		int borrowId = 0;
		Statement stmt = null ;
		 ResultSet rs2,rs3 = null;
		Title t=new Title();
		Date today = new Date();
		m=GetBook(b);
		if(m.getCommand().equals("Book not found,try again"))
		{
			m=new Message ("Book dosent exists,try again",null);
			return m;
		}
		t=(Title)m.getObjects();
		 m=getReader(r2.getReaderId());
		
		if(m.getCommand().equals("The user dont exist"))
		{
			m=new Message ("The user dont exist",null);
	 		return m; //user dosent exists
		}
		System.out.println("readername:");
		r=(Reader)m.getObjects();
		System.out.println("readername:"+r.getUsername()+" "+r.getStatus());
		
		 str="SELECT * from BorrowHistory  Where CatalogNumber ='"+t.getCatalogNumber() +"' and ReaderID='"+ r.getReaderId()+"'and actualReturnDate IS NULL ;";
		 try {
			stmt = conn.createStatement();
			
		   rs2 = stmt.executeQuery(str);
		   System.out.println(str);
		  
		   if(rs2.next())
			  {
			   System.out.println(rs2.getInt(1));
			   borrowId=rs2.getInt(1);
			   if(r.getStatus().equals("Frozen"))
	  		   {
				   
				if(r.getGraduate())//the reader is graduated 
				{
					str="SELECT count(*) from BorrowHistory  Where  ReaderID='"+ r.getReaderId()+"';";
					 try {
							stmt = conn.createStatement();
						
						   rs3 = stmt.executeQuery(str);
						   rs3.next();
						   if(rs3.getInt(1)==1)//the reader leand 1 book
						   { 
							   r.setStatus("Locked");
							   System.out.println("staus"+r.getStatus());
							   m=EditReader(r);//set reader status
							   System.out.println  ("staus back:"+((Reader)(m.getObjects())).getStatus());
							   t.setInStorage(t.getInStorage()+1);//add to storage book 1 
							   UpdateStortage(t.getCatalogNumber(),t.getInStorage());
							   m=new Message ("Now student is Locked Return book ok",null);
							   checkOrders( today,borrowId,t.getCatalogNumber(),disc);
							   return m; 
						   }
						   else{//the reader leanded >1 book
							   t.setInStorage(t.getInStorage()+1);//add to storage book 1 
							   UpdateStortage(t.getCatalogNumber(),t.getInStorage());
							   m=new Message ("there are still debts to the librar Return book ok",null);
							   checkOrders( today,borrowId,t.getCatalogNumber(),disc);
							   return m; 
							   
						   }
						   
					 }
					 catch (Exception e) {
							
							e.printStackTrace();
							return new Message("error",null);
						}
				}
				else{// the student ungraduanted
					str="SELECT count(*) from BorrowHistory  Where  ReaderID='"+ r.getReaderId()+"';";
					
					   rs3 = stmt.executeQuery(str);
					   rs3.next();
					   if(rs3.getInt(1)==1)//the reader leand 1 book
					   {
						   r.setStatus("Activated");
						   System.out.println("staus"+r.getStatus());
						   m=EditReader(r);//set reader status
						   t.setInStorage(t.getInStorage()+1);//add to storage book 1 
						   UpdateStortage(t.getCatalogNumber(),t.getInStorage());
						   m=new Message ("Now status  is Active Return book ok",null);
						   checkOrders( today,borrowId,t.getCatalogNumber(),disc);
						   return m; 
					   }
					   else{//the reader leanded >1 book
						   t.setInStorage(t.getInStorage()+1);//add to storage book 1 
						   UpdateStortage(t.getCatalogNumber(),t.getInStorage());
						   m=new Message ("There are still debts to the library Retorn book ok",null);
						   checkOrders( today,borrowId,t.getCatalogNumber(),disc);
						   return m; 
					   }
				}
	  		
	  	       }
			   else
			      {
				   t.setInStorage(t.getInStorage()+1);//add to storage book 1 
				   UpdateStortage(t.getCatalogNumber(),t.getInStorage());
				   m=new Message ("book returned",null);
				   checkOrders( today,borrowId,t.getCatalogNumber(),disc);
			 		return m; 
				   
			     }	   
			  }
		
		  else
		  {
			  
			  m=new Message ("The user id didn't lend this book",null);
		 		return m; //user dosent exists
		  }
	
		 } catch (Exception e) {
				
				e.printStackTrace();
				return new Message("error",null);
			}	
		 
 }
	/**called from ReturnBook() ,checks if an email message should be sent to a Reader that placed
	 * an order for the book returned and also update BorrrowHistory record
	 * @param date the date of the return
	 * @param borrowId thw borrowid from BorrowHistory
	 * @param catalogNumber The catalog number
	 * @param disc description,if librarian specified
	 */
	public void checkOrders(Date date,int borrowId,int catalogNumber,String disc)
	{ 
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		ResultSet rs5;
		Date today = new Date();
     	Statement stmt = null;
     	Title t1;
		String str;
		Boolean flag=true;
		try {
			stmt = conn.createStatement();
		} catch (Exception e1) {
			
			e1.printStackTrace();
		}

		str="UPDATE BorrowHistory SET actualReturnDate='"+ dateFormat.format(date) +"' , Comments ='"+disc+"' WHERE Id_Borrow='"+borrowId+"';";
		 System.out.println(str);
		 
		try {
			
		      stmt.executeUpdate(str);
		      
	   } catch (Exception e) {
				
				e.printStackTrace();
				
			}	
		t1=(Title)((Message)(GetBook(new BookItem(catalogNumber)))).getObjects();
		System.out.println(t1.getCatalogNumber());
		 str="SELECT * From Orders Where CatalogNumber ='"+catalogNumber +"'and  notified='0' ORDER BY date ASC, daytime ASC; ";	
		 try {
			rs5 = stmt.executeQuery(str);
			
			while(flag && rs5.next())
			{
				if(!rs5.getBoolean(6))
				{
				Reader r3=new Reader();
				r3=(Reader) (getReader(rs5.getInt(2))).getObjects();
				String mas;
				mas="The Book:"+t1.getName()+" id:"+t1.getCatalogNumber()+" has arrived to the library, and resereved for you for two days";
				sendMail(mas,r3.getEmail());
				flag=false;
				 str="UPDATE Orders SET  notified='1',DateOfNotification='"+dateFormat.format(today)+"' WHERE  OrderId ='"+ rs5.getInt(1)+"';";	
				 try {
					stmt.executeUpdate(str);
				 }catch (Exception e) {
						
						e.printStackTrace();
					}
				}
				else
				{
					rs5.next();
				}
			}
		} catch (Exception e) {
			
			e.printStackTrace();
			
		}
		
	}
	/** 
	 * edits the reader details
	 * @param reader the Reader object with the edited details 
	 * @return edit confirmation message
	 */
	public Message EditReader(Reader reader) {
		Message m = null;
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement("UPDATE Reader SET  UserName=?, Password=?, Cellphone=?, email=?, Graduate=?, IsConnected=?,  status=? WHERE  ReaderID =?");
			ps.setString(1, reader.getUsername());
			ps.setString(2,  reader.getPassword());
			ps.setString(3, reader.getCellphone());
		    ps.setString(4, reader.getEmail());
			ps.setBoolean(5, reader.getGraduate());
			ps.setInt(6, reader.getIsConnected());
			ps.setString(7,reader.getStatus());
			ps.setInt(8,reader.getReaderId());	
			ps.executeUpdate();
           }
		catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}
		m=new Message ("The Reader is edited",reader);
 		return m; 
	}
	/**
	 * send an email message to address specified in parameter
	 * @param message the email address
	 * @param toAddress receipt 
	 */
    public void sendMail(String message,String toAddress){
    	 String MAIL_SERVER = "smtp.gmail.com";
    	 String USERNAME = "madgirit@gmail.com";
    	 String PASSWORD = "tGAcc09V";
    	
    	try {
    		String fromAddress = "madgirit@gmail.com";
    		String subject = "Message from library";

    		Properties properties = System.getProperties();
    		properties.put("mail.smtps.host", MAIL_SERVER);
    		properties.put("mail.smtps.auth", "true");

    		Session session = Session.getInstance(properties);
    		MimeMessage msg = new MimeMessage(session);

    		msg.setFrom(new InternetAddress(fromAddress));
    		msg.addRecipients(javax.mail.Message.RecipientType.TO, toAddress);
    		msg.setSubject(subject);
    		msg.setText(message);

    		Transport tr = session.getTransport("smtps");
    		tr.connect(MAIL_SERVER, USERNAME, PASSWORD);
    		tr.sendMessage(msg, msg.getAllRecipients());
    		tr.close();
    		} catch (AddressException ex) {
    		System.out.println(ex.getMessage());
    		} catch (MessagingException ex) {
    		System.out.println(ex.getMessage());
    		
    		}
    		}
  /**called from ReturnBook,increase storage  		
   * @param catalognum the book to update storage
   * @param num the new storage value
   */
    public void UpdateStortage(int catalognum,int num) {
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement("UPDATE Book SET  inStorage=? WHERE  CatalogNumber =?");
			ps.setInt(1,num);
			ps.setInt(2, catalognum);
		    
            ps.executeUpdate();
				
			} catch (Exception e) {
				
				e.printStackTrace();
				
			}
		
    }
	/**
	 * Reader-edit function,Reader can edit only email and cellphone
	 * @param reader contains Reader Object with edited details
	 * @return edit confirmation
	 */
	public Message ViewReaderEdit(Reader reader) {
		Message m = null;
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement("UPDATE Reader SET  Cellphone=?, email=? WHERE  ReaderID =?");
			ps.setString(1, reader.getCellphone());
		    ps.setString(2, reader.getEmail());

			ps.setInt(3,reader.getReaderId());	
			ps.executeUpdate();
           }
		catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}
		m=new Message ("The Reader is edited",reader);
 		return m; 
	}
/**
 * gets reader history ,called every time a Reader Logs in
 * @param r contains the readerID that logged in
 * @return an Message Object with Reader object
 */
	public Message ReaderHistory(Reader r) {
		Message m;
		String str="SELECT * FROM BorrowHistory WHERE ReaderId = '"+ r.getReaderId()+"';";
		Statement stmt = null;
		ResultSet rs = null;
		BorrowHistory borrow;
		int numrows1 = 0;
		int i=0;
		
		try {
			stmt = conn.createStatement();
			 rs = stmt.executeQuery(str);
			if(!rs.next()){
				rs.close();
				 m= new Message("User has no history",null);
				return m;
			}
			
		} catch (Exception e){
			
			e.printStackTrace();
			return new Message("error",null);
		}
		try {
			rs.last();
			numrows1=rs.getRow();
			rs.beforeFirst();
		} catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}
		Object[] bh=new Object[numrows1];
		
		try {
			while (rs.next()){
				borrow=new BorrowHistory();
				borrow.setID(rs.getInt(1));
				borrow.setCatalogNumber(rs.getInt(2));
				borrow.setBorrowDate(rs.getDate(4));
				borrow.setReturnDate(rs.getDate(5));
				borrow.setActualReturnDate(rs.getDate(6));
				borrow.setComments(rs.getString(7));	
				bh[i]=borrow;
				System.out.println( ((BorrowHistory)bh[i]).getCatalogNumber());
				i++;
			}
			
			rs.close();
		} catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}
		m=new Message("History Found",bh);
		return  m;
	
		
	}
/**
 * When Librarian extend a reader borrow, this function is called,extension days are according to librarian input
 * @param r  the reader that will get the extension
 * @param h BorrowHistory Object to extend
 * @param l Librarian Object to record the librarian name(Extender name)
 * @return extend confirmation
 */
	public Message LibrarianExtend(Reader r, BorrowHistory h, Librarian l) {
		PreparedStatement ps;
		Calendar cal = Calendar.getInstance();
		
		cal.setTime(h.getReturnDate());
		try {
			ps = conn.prepareStatement("UPDATE BorrowHistory SET ReturnDate=?,LibrarianExtenderName=? WHERE ReaderID=? AND CatalogNumber=?");
			ps.setDate(1, new  java.sql.Date(cal.getTimeInMillis()));
			ps.setString(2, l.getFirstname());
			ps.setInt(3, r.getReaderId());
			ps.setInt(4, h.getCatalogNumber());
			ps.executeUpdate();	
		} catch (Exception e) {
			e.printStackTrace();
			return  new Message("Error Extending date",null);
		}	


		
		return new Message("Return date extended",null);
	}


/**
 * When a reader extend this function is called and when extension is allowed than 3 days are added to return date
 * @param r2 the reader to extend
 * @param h BorrowHistory Object to extend
 * @return extend confirmation
 */
	public Message ReaderExtend(Reader r2, BorrowHistory h) {
		String str="SELECT Status FROM Reader WHERE ReaderId = '"+ r2.getReaderId()+"';";
		Statement stmt = null;
		ResultSet rs = null;
		PreparedStatement ps;
		Calendar cal = Calendar.getInstance();
		cal.setTime(h.getReturnDate());
		System.out.println("1");
			try {
				stmt = conn.createStatement();
				 rs = stmt.executeQuery(str);
				 rs.next();
				if(rs.getString(1).equals("Frozen"))
				{
					rs.close();
						return new Message("Cant extend return date, Reader is frozen",null);
				}
				rs.close();
			} catch (Exception e) {
				
				e.printStackTrace();
				return new Message("error",null);
			}
			System.out.println("2");
			str="SELECT Wanted FROM Book WHERE CatalogNumber='"+h.getCatalogNumber()+"';";
			try {
				rs = stmt.executeQuery(str);
				 rs.next();
				 if(rs.getBoolean(1))
					{
						rs.close();
					return new Message("Cant extend return date, Book is Wanted",null);
					}
				 rs.close();
			} catch (Exception e) {
				
				e.printStackTrace();
				return new Message("error",null);
			}
			System.out.println("3");
			str="SELECT * FROM Orders WHERE CatalogNumber='"+h.getCatalogNumber()+"';";
			try {
				rs = stmt.executeQuery(str);
				 
				 if(rs.next())
					{
						rs.close();
					return new Message("Cant extend return date, Book has orders",null);
					}
				 rs.close();
			} catch (Exception e) {
				
				e.printStackTrace();
				return new Message("error",null);
			}
			
			
			long mili;
			Date d=h.getReturnDate();
			mili=d.getTime();
			mili=mili+259200000;
			d.setTime(mili);
			
			h.setReturnDate((java.sql.Date) d);
			
			cal.setTime(h.getReturnDate());
			
			try {
				ps = conn.prepareStatement("UPDATE BorrowHistory SET ReturnDate=? WHERE ReaderID=? AND CatalogNumber=?");
				System.out.println("not good");
				ps.setDate(1, new  java.sql.Date(cal.getTimeInMillis()));
				ps.setInt(2, r2.getReaderId());
				ps.setInt(3, h.getCatalogNumber());
				
				ps.executeUpdate();	
				System.out.println("good good");
			} catch (Exception e) {
               e.printStackTrace();
				return  new Message("Error Extending date",null);
			}	
			return new Message("Return date extended for 3 days",null);

	}
/**
 * gives the Librarian Manager list of all Reader and workers
 * @return Message Object with Reader and Librarian arrays
 */
	public Message ManagerGetDetails() {
		String str="SELECT * FROM Reader WHERE ReaderID NOT IN (SELECT ReaderID From Librarian) ;";
		Statement stmt = null;
		ResultSet rs1 = null,rs = null;
		int numrows1 = 0,numrows2=0;
		int i=0;
		Object o[]=new Object[2];
		
		try {
			stmt = conn.createStatement();
			 rs = stmt.executeQuery(str);
				rs.last();
				numrows1=rs.getRow();
				rs.beforeFirst();
		} catch (Exception e) {
			
			e.printStackTrace();
		}
		Reader[] r=new Reader[numrows1];
		try {
			while(rs.next()){
				r[i]=new Reader();
				r[i].setReaderId(rs.getInt(1));
				r[i].setUsername(rs.getString(2));
				r[i].setPassword(rs.getString(3));
				r[i].setCellphone(rs.getString(4));
				r[i].setEmail(rs.getString(5));
				r[i].setGraduate(rs.getBoolean(6));
				r[i].setIsConnected(rs.getInt(7));
				r[i].setStatus(rs.getString(8));
				i++;
				
			}
			o[0]=r;
			rs.close();
		} catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}
		
		
		str="SELECT * FROM Librarian l,Reader r WHERE l.ReaderID IN (SELECT ReaderID From Reader) AND l.ReaderID=r.ReaderID ;";
		 try {
			rs1 = stmt.executeQuery(str);
			rs1.last();
			numrows2=rs1.getRow();
			rs1.beforeFirst();
		} catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}
		
		 Librarian[] l=new Librarian[numrows2];
		 i=0;
		 try {
			while(rs1.next())
			{
				l[i]=new Librarian();
				l[i].setWorkerID(rs1.getInt(1));
				l[i].setReaderId(rs1.getInt(2));
				l[i].setFirstname(rs1.getString(4));
				l[i].setLastname(rs1.getString(5));
				l[i].setUsername(rs1.getString(7));
				l[i].setPassword(rs1.getString(8));
				l[i].setCellphone(rs1.getString(9));
				l[i].setEmail(rs1.getString(10));
				l[i].setGraduate(rs1.getBoolean(11));
				l[i].setStatus(rs1.getString(13));
				i++; 
			 }
			 rs1.close();
		} catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}
		 o[1]=l;
		return new Message("Returned Readers and Workers",o);
	}
/**
 * generate periodic report ,save it to database
 * every time this function is called a new report is made from database and 
 * is returned with all old reports
 * @return all periodic reports including the new one
 */
	public Message GetPeriodicReport() {
		Statement stmt = null;
		ResultSet rs3=null,rs2=null,rs = null,rs1 = null;
		PeriodicReport pr=new PeriodicReport();
		Date today =new Date();
		int i=0;
		PeriodicReport[] Periodic = null;
		
		Calendar cal = Calendar.getInstance();
		cal.setTime(today);
		pr.setReportDate(new  java.sql.Date(cal.getTimeInMillis()));
		PreparedStatement ps;
		int numrows1 = 0;
		String str="SELECT Count(ReaderID),status FROM Reader Group By status";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(str);
			rs.last();
			numrows1=rs.getRow();
			rs.beforeFirst();
			rs.next();
			if(numrows1==1){ //only Activated Readers
				
				pr.setActivatedReaders(rs.getInt(1));
				rs.close();
				pr.setFrozenReaders(0); 
				pr.setLockedReaders(0);
			}
			if(numrows1==2){
				pr.setActivatedReaders(rs.getInt(1));
				rs.next();
				pr.setFrozenReaders(rs.getInt(1));
				rs.close();
				pr.setLockedReaders(0);
				
			}
			if(numrows1==3){
				pr.setActivatedReaders(rs.getInt(1));
				rs.next();
				pr.setFrozenReaders(rs.getInt(1));
				rs.next();
				pr.setLockedReaders(rs.getInt(1));
				rs.close();
				
			}
		} catch (Exception e) {
			
			e.printStackTrace();
		}
		 str="SELECT Count(Id_Borrow) FROM BorrowHistory WHERE actualReturnDate IS NULL";
		 try {
			rs1 = stmt.executeQuery(str);
			rs1.next();
			pr.setNumberOfBorrowedBooks(rs1.getInt(1));
			rs1.close();
		} catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}
		 
		 str="SELECT Count(Id_Borrow) FROM BorrowHistory WHERE (ReturnDate <CURRENT_DATE and actualReturnDate IS NULL) or (returnDate<actualReturnDate)";
		 try {
			rs2= stmt.executeQuery(str);
			rs2.next();
			pr.setNumberOfDelayedInReturns(rs2.getInt(1));
			rs2.close();
		} catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}
		 
		 try {
			ps = conn.prepareStatement("INSERT INTO PeriodicReport (ActivatedReaders, FrozenReaders, LockedReaders, NumberOfBorrowedBooks, NumberOfDelayedInReturns, ReportDate)VALUES ( ? , ? , ? , ? , ? , ? ) ");
			ps.setInt(1,pr.getActivatedReaders());
			ps.setInt(2,pr.getFrozenReaders());
			ps.setInt(3,pr.getLockedReaders());
			ps.setInt(4,pr.getNumberOfBorrowedBooks());
			ps.setInt(5,pr.getNumberOfDelayedInReturns());
			ps.setDate(6,pr.getReportDate());
			ps.executeUpdate();	
		} catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}
		 
		 str="SELECT * FROM PeriodicReport order by ReportDate DESC";
		 try {
			rs3= stmt.executeQuery(str);
			rs3.last();
			numrows1=rs3.getRow();
			rs3.beforeFirst();
			Periodic=new PeriodicReport[numrows1];
			
			while(rs3.next())
			{
				Periodic[i]=new PeriodicReport();
				Periodic[i].setReportId(rs3.getInt(1));
				Periodic[i].setActivatedReaders(rs3.getInt(2));
				Periodic[i].setFrozenReaders(rs3.getInt(3));
				Periodic[i].setLockedReaders(rs3.getInt(4));
				Periodic[i].setNumberOfBorrowedBooks(rs3.getInt(5));
				Periodic[i].setNumberOfDelayedInReturns(rs3.getInt(6));
				Periodic[i].setReportDate(rs3.getDate(7));
				i++;
			}
			rs3.close();
		} catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}
		 
		 
		return new Message("New report added and returned all periodic report",Periodic);
	}
	
/**
 * generate lends report
 * @return Message object with report
 */
public Message GetLendReport() {
	Statement stmt = null;
	new Date();
	LendReport l=new LendReport();
	int numrows2=0;
	ResultSet rs1,rs2,rs3 = null;
	String str;
	str="SELECT avg(datediff(actualReturnDate,borrowDate)) FROM  BorrowHistory b1 , Book b2 WHERE b1.CatalogNumber=b2.CatalogNumber and b1.actualReturnDate is not null and b2.Wanted='1';";
	try {
		stmt = conn.createStatement();
		rs1 = stmt.executeQuery(str);
	   if(rs1.next())
	   {
		   l.setWantedAvg(rs1.getInt(1));
	   }
		rs1.close();
	} catch (Exception e) {
		
		e.printStackTrace();
		return new Message("error",null);
	}
		str="SELECT avg(datediff(actualReturnDate,borrowDate)) FROM  BorrowHistory b1 , Book b2 WHERE b1.CatalogNumber=b2.CatalogNumber and b1.actualReturnDate is not null and b2.Wanted='0';";
		try {
			rs2 = stmt.executeQuery(str);
		   if(rs2.next())
		   {
			   l.setRegularAvg(rs2.getInt(1));
		   }
			rs2.close();
		} catch (Exception e) {
			
			e.printStackTrace();
			return new Message("error",null);
		}
		str="SELECT datediff(actualReturnDate,borrowDate) as s FROM  BorrowHistory b1 , Book b2 WHERE b1.CatalogNumber=b2.CatalogNumber and b1.actualReturnDate is not null and b2.Wanted='1' ORDER BY s;";
		
		try {
			rs3 = stmt.executeQuery(str);
			rs3.last();
			numrows2=rs3.getRow();
			if(numrows2%2==1)
			{
				numrows2++;
			}
			numrows2=numrows2/2;
		   rs3.beforeFirst();
		   for(int i=0;i<numrows2;i++)
			rs3.next();
		   
			   l.setWantedMedian(rs3.getInt(1));
		   
			rs3.close();
			
		
	} catch (Exception e) {
		
		e.printStackTrace();
		return new Message("error",null);
	}
	str="SELECT datediff(actualReturnDate,borrowDate) as s FROM  BorrowHistory b1 , Book b2 WHERE b1.CatalogNumber=b2.CatalogNumber and b1.actualReturnDate is not null and b2.Wanted='0' ORDER BY s;";
		
		try {
			rs3 = stmt.executeQuery(str);
			rs3.last();
			numrows2=rs3.getRow();
			if(numrows2%2==1)
			{
				numrows2++;
			}
			numrows2/=2;
		   rs3.beforeFirst();
		   
		   for(int i=0;i<numrows2;i++)
			rs3.next();
			l.setRegularMedian(rs3.getInt(1));
			rs3.close();
			
		
	} catch (Exception e) {
		
		e.printStackTrace();
		return new Message("error",null);
	}
		l.setReportDate(new java.sql.Date (Calendar.getInstance().getTime().getTime()));
		System.out.print("Testing this row");
		return new Message("Returned Lend Report",l);
		
	
	}
/**
 * Generate late in returns report
 * @return Message Object with report
 */
public Message GetLateInReturnReport() {
	String str="select t.CatalogNumber,avg(diff) as avg,count(diff) from(SELECT CatalogNumber,(CASE  When actualReturnDate IS NULL Then DATEDIFF(CURRENT_DATE ,returnDate )Else DATEDIFF(actualReturnDate ,returnDate) END)as diff FROM BorrowHistory WHERE (returnDate < CURRENT_DATE and actualReturnDate IS NULL)or(returnDate<actualReturnDate) ) as t group by CatalogNumber ORDER BY avg;";
	Statement stmt= null,stmt2 = null;
	ResultSet rs,rs1;
	int sumofavg=0;
	int sumNumberOfDelays=0;
	int numrows,numrows2,i=1;
	LateInReturnReport[] lr = null;
	try {
		stmt = conn.createStatement();
		stmt2=conn.createStatement();
		rs = stmt.executeQuery(str);
		rs.last();
		numrows=rs.getRow();
		rs.beforeFirst();
	    lr=new LateInReturnReport[numrows+1];
	    
		while(rs.next()){
			lr[i]=new LateInReturnReport();
			lr[i].setBookCatalog(rs.getInt(1));
			lr[i].setAvg(rs.getInt(2));
			lr[i].setNumberOfDelays(rs.getInt(3));
			sumofavg=sumofavg+lr[i].getAvg()*lr[i].getNumberOfDelays();
			sumNumberOfDelays=sumNumberOfDelays+lr[i].getNumberOfDelays();
			str="SELECT  CatalogNumber , (CASE  When actualReturnDate IS NULL Then DATEDIFF(CURRENT_DATE ,returnDate )Else DATEDIFF(actualReturnDate ,returnDate) END)as diff FROM BorrowHistory WHERE ((returnDate < CURRENT_DATE AND actualReturnDate IS NULL) OR (returnDate < actualReturnDate)) AND  (CatalogNumber = '"+rs.getInt(1)+"' )ORDER BY diff ASC";
			rs1=stmt2.executeQuery(str);
			rs1.last();
			numrows2=rs1.getRow();
			rs1.beforeFirst();
			if(numrows2%2==1)
			{
				numrows2++;
			}
			numrows2=numrows2/2;
			for(int j=0;j<numrows2;j++)
				rs1.next();
			lr[i].setMedian(rs1.getInt(2));
			i++;
			rs1.close();
		}
		
	} catch (Exception e) {
		
		e.printStackTrace();
		return new Message("error",null);
	}
	i=i-1;
	if(i%2==1)
	{
		i++;
	}
	i=i/2;
	lr[0]=new LateInReturnReport();
	lr[0].setMedian(lr[i].getAvg());
	lr[0].setNumberOfDelays(sumNumberOfDelays);
	lr[0].setAvg(sumofavg/sumNumberOfDelays);
	lr[0].setReportDate(new java.sql.Date (Calendar.getInstance().getTime().getTime()));
	
	
	System.out.println(lr[0].getMedian());
	
	return new Message("Returned Lend Report",lr);
}
/**
 * when a reader graduate ,the method is called from an outer system and asks to change reader status to locked
 * the method checks for the conditions and change status accordingly
 * @param ReaderId reader id to lock
 * @return changed status confirmation
 */
public Message ExternalLockRequest(int ReaderId) {
	String str="SELECT * FROM BorrowHistory WHERE ReaderId='"+ReaderId+"' AND actualReturnDate IS NULL;";
	Statement stmt= null;
	String status;
	Message m = null;
	ResultSet rs;
	PreparedStatement ps;
	try {
		stmt = conn.createStatement();
		rs = stmt.executeQuery(str);
		if(rs.next())
		{	
			status="Frozen";
			m= new Message("The user still owes books to library so his status changed to Frozen!",null);
		}
		else
		{	
			status="Locked";
			m=new Message("Reader has no borrows so Status changed to Locked",null);
		}
		rs.close();
			try {		
				ps = conn.prepareStatement("UPDATE Reader SET status=?,Graduate=? WHERE ReaderID=?");
				ps.setString(1,status);
				ps.setBoolean(2,true);
				ps.setInt(3, ReaderId);
				ps.executeUpdate();	
			
			} catch (Exception e) {
				
				e.printStackTrace();
				return new Message("error",null);
			}			
		
	} catch (Exception e) {
		
		e.printStackTrace();
		return new Message("error",null);
	}
	
	
	return m;
}

	
}
